﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Xml;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Xml.Xsl;
using Sagacity.NC.Web.FormForm;

namespace Sagacity.NC.Web.Form
{
    /// <summary>
    /// 表单Excel帮助类
    /// </summary>
    public class FormExcelHelper
    {
        public Business.SysMange.Common mCommon = new Sagacity.NC.Business.SysMange.Common();
        private Excel.Application myExcel = null;

        public int _colFilterKey = 0;
        public string _colFilterIdentify = "";

        public FormExcelHelper() {
        
        }

        public FormExcelHelper(int FilterKey, string FilterIdentify)
        {
            _colFilterKey = FilterKey;
            _colFilterIdentify = FilterIdentify;         
        }
        /// <summary>
        /// 保存导入数据
        /// </summary>
        public bool SaveImportData(XmlNode xml, string formBaseInfo, Hashtable other)
        {
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(formBaseInfo);
            XmlNode table = doc.SelectSingleNode("Root/TableInfo");

            Sagacity.Entity.Build.ResourceInfo resourceInfo = (new Sagacity.Build.Application.Resource()).GetResrouse(int.Parse(table.Attributes["Resource"].Value));
            List<Sagacity.Entity.Build.QueryFieldInfo> fields = resourceInfo.Fields.FindAll(o => o.IsEnableEdit == true);

            string temp = "into " + table.Attributes["Name"].Value + " ( " + table.Attributes["KeyName"].Value + ",";
            foreach (Sagacity.Entity.Build.QueryFieldInfo q in fields)
            {
                temp += q.FieldName + ",";
            }
            foreach (string key in other.Keys)
            {
                temp += key + ",";
            }
            temp += ") values ( ";
            temp = temp.Remove(temp.LastIndexOf(","), 1);

            #region 生成批量新增的SQL语句
            StringBuilder builders = new StringBuilder();
            builders.Append("insert all ");

            XmlNodeList list = xml.SelectNodes("records/record");
            int count = 0;
            int[] counterList = new int[list.Count];
            foreach (XmlNode n in list)
            {
                StringBuilder builder = new StringBuilder();
                builder.Append(temp);
                counterList[count] = Business.SysMange.Common.GetCounter(table.Attributes["Name"].Value);
                builder.Append(counterList[count] + ",");
                count++;

                foreach (Sagacity.Entity.Build.QueryFieldInfo q in fields)
                {
                    XmlNode nq = n.SelectSingleNode(q.FieldName);
                    DataTable dt = null;
                    switch (q.FieldControlType)
                    {
                        case "Combox":
                            dt = mCommon.GetSQLResult(q.FieldDataSource,null);
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                if (dt.Rows[i]["Caption"].ToString() == nq.InnerText.Replace(" ", ""))
                                {
                                    builder.Append("'" + dt.Rows[i]["Id"].ToString() + "',");
                                    break;
                                }
                            }
                            break;
                        case "Tree":
                            dt = mCommon.GetSQLResult(q.FieldDataSource,null);
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                if (dt.Rows[i]["Caption"].ToString() == nq.InnerText.Replace(" ", ""))
                                {
                                    builder.Append("'" + dt.Rows[i]["Id"].ToString() + "',");
                                    break;
                                }
                            }
                            break;
                        case "DateTime":
                            if (nq.InnerText == null || nq.InnerText == "")
                                builder.Append("'',");
                            else
                                builder.Append("'" + DateTime.Parse(nq.InnerText).ToString("yyyy-MM-dd") + "',");
                            break;
                        default:
                            builder.Append("'" + nq.InnerText + "',");
                            break;
                    }
                }

                foreach (string key in other.Keys)
                {
                    builder.Append("'" + other[key].ToString() + "',");
                }
                builder.Append(" ) ");
                builders.Append(builder.ToString().Remove(builder.ToString().LastIndexOf(","), 1));
            }
            builders.Append(" select 1 from dual ");
            #endregion

            #region 生成批量新增时的Log的SQL语句
            StringBuilder buildLogs = new StringBuilder();
            buildLogs.Append("insert all ");
            if (table.Attributes["Log"].Value == "true")
            {
                for (int i = 0; i < counterList.Length; i++)
                {
                    buildLogs.Append(
                    string.Format(@" into CFG_LOG (LOG_ID,RESOURCE_ID,DATA_ID,OPERATORTYPE,OPERATORID,OPERATORDATE)
                        values({0},{1},{2},{3},{4},'{5}') ", Business.SysMange.Common.GetCounter("CFG_LOG"), table.Attributes["Resource"].Value,
                       counterList[i], "0", table.Attributes["CurrentUser"].Value, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                }
            }
            buildLogs.Append(" select 1 from dual ");
            #endregion

            return Business.SysMange.Common.ExcuteFormCommand(builders.ToString(), new Hashtable(), buildLogs.ToString());
        }

        /// <summary>
        /// 导出数据到客户端
        /// </summary>
        public void OutPutExcel(Sagacity.Entity.Build.RoleInfo Role, int ResourceId,
            List<Sagacity.Entity.Query.FilterInfo> filters, System.Web.HttpResponse response, string ModelPath, 
            List<Sagacity.Entity.Query.ParameterInfo> paramters)
        {
            XmlDocument xmldoc = new XmlDocument();
            xmldoc.Load(ModelPath);
            XmlNode node = xmldoc.ChildNodes[1].ChildNodes[3].ChildNodes[0];
            node.InnerXml = this.ResourceToDT(Role, ResourceId, filters,paramters);


            XslCompiledTransform xmlcom = new XslCompiledTransform();
            response.ContentType = "application/vnd.ms-excel";
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.AddHeader("Content-Disposition", "attachment; filename="+ HttpUtility.UrlEncode("submittedData.xls", System.Text.Encoding.UTF8));
            response.Charset = "";
            response.Write(xmldoc.InnerXml);
            response.Flush();
            response.End();
        }

        /// <summary>
        /// 将资源转换成DataTable。
        /// </summary>
        /// <returns></returns>
        protected string ResourceToDT(Sagacity.Entity.Build.RoleInfo roleInfo, int resourceId, List<Sagacity.Entity.Query.FilterInfo> filters,List<Sagacity.Entity.Query.ParameterInfo> paramters)
        {
            Sagacity.Entity.Build.ResourceInfo resourceInfo = new Sagacity.Entity.Build.ResourceInfo();
            resourceInfo = (new Sagacity.Build.Application.Resource()).GetResrouse(resourceId);
            List<Sagacity.Entity.Build.QueryFieldInfo> fields = resourceInfo.Fields;
            resourceInfo.Model.Filters = filters;
            resourceInfo.Model.Parameters = paramters;

            DataTable dt = (new Sagacity.Build.Role()).GetFormRightResourceDataTable(resourceInfo, roleInfo);


            //if (!string.IsNullOrEmpty(_colFilterIdentify) && _colFilterKey != 0)
            //{
            //    FormHelper helper = new FormHelper(_colFilterKey, _colFilterIdentify, null);
            //    fields = helper.FilterColumn(fields, resourceId).FindAll(o => o.IsHidden == false);
            //}
            //else 
            //{ 
                
            //}

            fields = fields.FindAll(o => o.IsHidden == false);

            foreach (Sagacity.Entity.Build.QueryFieldInfo q in fields)
            {
                if (q.IsHidden == true)
                {
                    dt.Columns.Remove(q.FieldName);
                }
                else
                {
                    dt.Columns[q.FieldName].Caption = q.FieldCaption;
                }
            }

            return ExportDataTable(dt, fields);
        }

        /// <summary>
        /// 生成导出的数据格式
        /// </summary>
        protected string ExportDataTable(System.Data.DataTable dt, List<Sagacity.Entity.Build.QueryFieldInfo> fields)
        {
            System.Text.StringBuilder builder = new System.Text.StringBuilder();

            //生成列宽
            for (int i = 0; i < fields.Count; i++)
            {
                builder.Append("<Column ss:Width=\"" + (fields[i].FieldWidth == 0 ? 100 : fields[i].FieldWidth).ToString() + "\"/>");
            }

            //生成列表表头信息
            //builder.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"22.5\"><Cell ss:MergeAcross=\"" + (fields.Count - 1) + "\" ss:StyleID=\"s63\"><Data ss:Type=\"String\">" + excelName + "</Data></Cell></Row>");

            //生成日期信息
            //builder.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"22.5\"><Cell ss:MergeAcross=\"" + (fields.Count - 1) + "\" ss:StyleID=\"s65\"><Data ss:Type=\"String\">" + DateTime.Now.ToLongDateString() + "</Data></Cell></Row>");

            //生成列表表头信息
            builder.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"18.75\">");
            for (int i = 0; i < fields.Count; i++)
            {
                builder.Append("<Cell ss:StyleID=\"s67\"><Data ss:Type=\"String\">" + fields[i].FieldCaption + "</Data></Cell>");
                if (HasHelpCaption(fields[i]))
                {
                    fields[i].FieldName = "Caption_" + fields[i].FieldName;
                }
            }
            builder.Append("</Row>");

            //生成列表数据信息
            foreach (DataRow r in dt.Rows)
            {
                builder.Append("<Row ss:AutoFitHeight=\"0\" ss:Height=\"18.75\">");
                for (int i = 0; i < fields.Count; i++)
                {
                    string vText = r[fields[i].FieldName].ToString().Replace("<", "(");
                    vText = vText.Replace(">", ")");
                    builder.Append("<Cell ss:StyleID=\"s67\"><Data ss:Type=\"String\">" + vText + "</Data></Cell>");
                }
                builder.Append("</Row>");
            }
            return builder.ToString();
        }

        public bool HasHelpCaption(Sagacity.Entity.Build.QueryFieldInfo qf)
        {
            return qf.FieldDataSource != null && !string.IsNullOrEmpty(qf.FieldDataSource.Trim()) && qf.FieldControlType != "Button" && qf.FieldControlType != "Formula";
        }

        /// <summary>
        /// 得到Excel文件中某一张工作表的表格
        /// </summary>
        /// <param name="filename">Excel文件路径</param>
        /// <param name="sheetNum">工作表编号</param>
        /// <returns>所有数据生成的表格</returns>
        public System.Data.DataTable GetExcelTable(string filename, int sheetNum)
        {
            object missing = System.Reflection.Missing.Value;
            myExcel = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbook myBook = myExcel.Application.Workbooks.Open(filename, missing, missing, missing,
             missing, missing, missing, missing, missing, missing,
             missing, missing, missing, missing, missing);  //打开Excel文件

            if (myBook != null)   //打开成功
            {
                myExcel.Visible = false;

                Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[sheetNum];  //得到工作表
                System.Data.DataTable dt = new System.Data.DataTable();

                for (int j = 1; j <= mySheet.Cells.CurrentRegion.Columns.Count; j++)
                    dt.Columns.Add();

                for (int i = 1; i <= mySheet.Cells.CurrentRegion.Rows.Count; i++)   //把工作表导入DataTable中
                {
                    DataRow myRow = dt.NewRow();

                    for (int j = 1; j <= mySheet.Cells.CurrentRegion.Columns.Count; j++)
                    {
                        Excel.Range temp = (Excel.Range)mySheet.Cells[i, j];
                        string strValue = temp.Text.ToString();
                        myRow[j - 1] = strValue;
                    }
                    dt.Rows.Add(myRow);
                }
                myExcel.Quit();  //退出Excel文件

                System.Diagnostics.Process[] myProcesses = System.Diagnostics.Process.GetProcessesByName("EXCEL");
                foreach (System.Diagnostics.Process instance in myProcesses)
                {
                    //
                   // instance.Kill();
                }

                return dt;
            }

            //打开不成功
            return null;
        }
    }
}